const mysql = require('mysql') // 引入的mysql库
const config = require('./config')
const debug = require('../utils/constant').debug
const {isObject} = require('../utils')

function  connect() {
    return mysql.createConnection({
        host: config.host,
        user: config.user,
        password: config.password,
        database: config.database,
        multipleStatements: true
    })
}

function querySql(sql) {
    const conn = connect()
    debug && console.log(sql)
    return new Promise((resolve, reject) => {
        try{
            conn.query(sql,(err, results)=>{
                if(err){
                    // 如果出现错误
                    debug && console.log('查询失败，原因:' + JSON.stringify(err))
                    reject(err)
                }else{
                    // 如果正常 就resolve结果
                    debug && console.log('查询成功', JSON.stringify(results))
                    resolve(results)
                }
            })
        }catch(e){
            reject(e)
        }finally {
            // 不论sql执行成功与否，都要关闭conn链接，不然会导致内存泄漏
            conn.end()
        }
    })
}

function queryOne(sql){
    return new Promise((resolve, reject) => {
        querySql(sql).then(results => {
            if(results && results.length>0){
                resolve(results[0])
            }else{
                resolve(null)
            }
        }).catch(err => {
            reject(err)
        })
    })
}
// 插入数据库
function insert(model, tableName){
    return new Promise((reslove, reject)=>{
        if(!isObject(model)){
            reject(new Error('插入数据库失败，model是非对象'))
        }else{
            const keys =[]
            const values =[]
            Object.keys(model).forEach(key=>{
                // 验证是自身属性 不是继承来的
                if(model.hasOwnProperty(key)){
                    keys.push(`\`${key}\``)
                    values.push(`'${model[key]}'`)
                }
            })
            if(keys.length > 0 && values.length > 0){
                let sql = `INSERT INTO \`${tableName}\` (`
                const keysString = keys.join(',')
                const valuesString = values.join(',')
                sql = `${sql}${keysString}) VALUES (${valuesString})`
                // 链接数据库
                const conn = connect()
                try{
                    conn.query(sql,(err, result)=>{
                        if(err){
                            reject(err)
                        }else{
                            reslove(result)
                        }
                    })
                }catch(e){
                    reject(e)
                }finally {
                    conn.end()
                }
            }else{
                reject(new Error('SQL解析失败'))
            }

        }
    })
}

//update
function update(model, tableName, where){
    return new Promise((reslove, reject)=>{
        if(!isObject(model)){
            reject(new Error('插入数据库失败，model是非对象'))
        }else{
            const entry =[]
            Object.keys(model).forEach(key=>{
                // 验证是自身属性 不是继承来的
                if(model.hasOwnProperty(key)){
                    entry.push(`\`${key}\`='${model[key]}'`)
                }
            })
            if(entry.length > 0){
                let sql = `UPDATE \`${tableName}\` SET`
                sql = `${sql}${entry.join(',')}`
                // 链接数据库
                const conn = connect()
                try{
                    conn.query(sql,(err, result)=>{
                        if(err){
                            reject(err)
                        }else{
                            reslove(result)
                        }
                    })
                }catch(e){
                    reject(e)
                }finally {
                    conn.end()
                }
            }else{
                reject(new Error('SQL解析失败'))
            }

        }
    })
}

function and(where,k,v){
    if(where === "where"){
        return `${where} \`${k}\`='${v}'`
    }else{
        return `${where} and \`${k}\`='${v}'`
    }
}

function andLike(where,k,v){
    if(where === "where"){
        return `${where} \`${k}\` Like '%${v}%'`
    }else{
        return `${where} and \`${k}\` Like '%${v}%'`
    }
}

module.exports = {
    querySql,
    queryOne,
    insert,
    update,
    and,
    andLike
}